# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://doc.scrapy.org/en/latest/topics/item-pipeline.html
import scrapy
from . items import Book,Chapter,Article


from twisted.enterprise import adbapi
import pymysql
class MysqlPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):  # 函数名固定，会被scrapy调用，直接可用settings的值
        """
        数据库建立连接
        :param settings: 配置参数
        :return: 实例化参数
        """
        adbparams = dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWORD'],
            port=settings['MYSQL_PORT'],
            charset="utf8",
            cursorclass=pymysql.cursors.DictCursor  # 指定cursor类型
        )
        # 连接数据池ConnectionPool，使用pymysql或者Mysqldb连接
        dbpool = adbapi.ConnectionPool('pymysql', **adbparams)
        # 返回实例化参数
        return cls(dbpool)

    def process_item(self, item, spider):
        """
        使用twisted将MySQL插入变成异步执行。通过连接池执行具体的sql操作，返回一个对象
        """
        query = self.dbpool.runInteraction(self.do_insert, item)  # 指定操作方法和操作数据
        # 添加异常处理
        query.addCallback(self.handle_error)  # 处理异常

    def do_insert(self, cursor, item):
        # 对数据库进行插入操作，并不需要commit，twisted会自动commit
        if item.__class__ == Book:
            item["name"] = item["name"].replace("'", "\\\'")
            insert_sql = """
                       insert into story_book(name,book_type,sequence,is_active,add_time,cover_img,
                       author,update_time,`desc`,last_chapterid,last_chapter,user_id,third_book_id)VALUES("%s",%s,%s,%s,%s,"%s","%s",%s,"%s",%s,"%s",%s,%s)
                   """ % (item['name'], item['book_type'], item['sequence'], item['is_active'],
                          item['add_time'], item['cover_img'], item['author'], item['update_time'], item['desc'],
                          item['last_chapterid'], item['last_chapter'], item['user_id'], item['third_book_id'])
            cursor.execute(insert_sql)
        if item.__class__ == Chapter:
            item["name"] = item["name"].replace("'", "\\\'")
            insert_sql = """
                                   insert into story_chapter(book_id,name,level_id,paths,parent_id,order_num,
                                   add_time,user_id,size,sale_price,sale_num,third_chapter_id)VALUES(%s,"%s",%s,"%s",%s,%s,%s,%s,%s,%s,%s,%s)
                               """ % (item['book_id'], item['name'], item['level_id'], item['paths'],
                                      item['parent_id'], item['order_num'], item['add_time'], item['user_id'],
                                      item['size'],
                                      item['sale_price'], item['sale_num'], item['third_chapter_id'])
            cursor.execute(insert_sql)
        if item.__class__ ==Article:

            try:
                item["content"]=item["content"].replace("'", "\\\'")
                insert_sql = """
                                insert into story_article(chapter_id,user_id,content,book_id)VALUES(%s,%s,'%s',%s) 
                                """ % (item["chapter_id"], item["user_id"], item["content"], item["book_id"])

                cursor.execute(insert_sql)
            except Exception as e:
                print(e)
                self.write(str(insert_sql))
                self.write(str(e))
    def handle_error(self, failure):
        if failure:
            # 打印错误信息
            print(failure)
            fo = open("foo.txt", "a", encoding="utf-8")
            fo.write(failure)
            fo.write("\n")
            # 关闭打开的文件
            fo.close()
    def write(self,is_str):
        fo = open("foo.txt", "a", encoding="utf-8")
        fo.write(is_str)
        fo.write("\n")

        # 关闭打开的文件
        fo.close()

